import datetime
import time

from pymysql import connect


def Link_database():
    """链接数据库"""
    # con = connect(host='rm-m5ev2j6ywo4m51ebu5o.mysql.rds.aliyuncs.com', user='root', passwd='112358Ys', db='djt',
    #               charset='utf8')
    con = connect(host='193.112.133.62', user='ydsp', password='ydsp123456', db='djt', charset='utf8')
    # con = connect(host='193.112.133.62', user='admin', password='admin888', db='admin', charset='utf8')

    obj = con.cursor()  # 游标对象
    return con, obj


def OeratingSQL(data_list, user_id, store_name):
    """运营数据存储"""
    con, obj = Link_database()  # 链接数据库

    # data_dict = json.loads(data_str)
    for data in data_list:
        # 添加字段
        data["statDate"] = int(time.mktime(time.strptime(data["statDate"], "%Y-%m-%d")))  # 转化为时间戳

        sql = """insert into operating values(0, %d, %d, %d, %d, %d,
                %f, %f, %f, %d, %d, %f, %d, %d, %f,%d, %d, '%s')
                """ % (data["statDate"], data["store_product_cou"], data["payItemcun"],
                       data["browse_number"], data["visitors"], data["payment"],
                       data["paymentRate"], data["successful_refund"], data["buyers"],
                       data["old_buyers"], data["Unit_price"], data["payment_Number"],
                       data["add_pay_number"], data["Refund"], data["Item_cnt"],
                       user_id, store_name)

        obj.execute(sql)  # 数据库操作

    con.commit()  # 事物提交
    obj.close()
    con.close()


def TrafficSQL(data, user_id, store_name):
    """流量数据存储"""
    con, obj = Link_database()  # 链接数据库

    # for data_dict in data["table"]["PC"]["data"]:
    #     statdate = int(data_dict["statDate"]["value"] / 1000)
    #     pagename = data_dict["pageName"]
    #     visitors = data_dict["uv"]["value"]
    #     visitors_rate = data_dict["uv"]["cycleCrc"]
    #     order_buyers = data_dict["crtByrCnt"]["value"]
    #     order_buyers_rate = data_dict["crtByrCnt"]["cycleCrc"] if data_dict["crtByrCnt"]["cycleCrc"] else 0.0
    #     order_conversion = data_dict["crtRate"]["value"] if data_dict["crtRate"]["value"] else 0.0
    #     order_conversion_rate = data_dict["crtRate"]["cycleCrc"] if data_dict["crtRate"]["cycleCrc"] else 0.0
    #     use_date = int(time.mktime(time.strptime(datetime.datetime.now().strftime("%Y-%m-%d"), '%Y-%m-%d')))
    #     user = user_id
    #
    #     sql_1 = """insert into traffic values ('table', 'PC', %d, '%s', %d, %f, %d, %f, %f, %f, %d, %d)
    #             """ % (statdate, pagename, visitors, visitors_rate, order_buyers,
    #            order_buyers_rate, order_conversion, order_conversion_rate,
    #            use_date, user)
    #
    #     obj.execute(sql_1)  # 数据库操作
    #
    # for data_dict in data["table"]["WX"]["data"]:
    #     statdate = int(data_dict["statDate"]["value"] / 1000)
    #     pagename = data_dict["pageName"]
    #     visitors = data_dict["uv"]["value"]
    #     visitors_rate = data_dict["uv"]["cycleCrc"]
    #     order_buyers = data_dict["crtByrCnt"]["value"]
    #     order_buyers_rate = data_dict["crtByrCnt"]["cycleCrc"] if data_dict["crtByrCnt"]["cycleCrc"] else 0
    #     order_conversion = data_dict["crtRate"]["value"] if data_dict["crtRate"]["value"] else 0
    #     order_conversion_rate = data_dict["crtRate"]["cycleCrc"] if data_dict["crtRate"]["cycleCrc"] else 0
    #     use_date = int(time.mktime(time.strptime(datetime.datetime.now().strftime("%Y-%m-%d"), '%Y-%m-%d')))
    #     user = user_id
    #
    #     sql_2 = """insert into traffic values("table", "WX", %d, '%s', %d,
    #             %f, %d, %f, %f, %f, %d, %d)
    #             """ % (statdate, pagename, visitors, visitors_rate, order_buyers,
    #                    order_buyers_rate, order_conversion, order_conversion_rate,
    #                    use_date, user)
    #
    #     obj.execute(sql_2)
    # print(data)
    # for data_list in data:
    #     for data_dict in data_list:
    #         for i in data_dict:
    #             statdate = int(data_dict[i]['statDate']['value'] / 1000)
    #             pagename = data_dict[i]["pageName"]
    #             visitors = data_dict[i]["uv"]["value"]
    #             visitors_rate = data_dict[i]["uv"]["cycleCrc"] if data_dict[i]["uv"]["cycleCrc"] else 0.0
    #             order_buyers = data_dict[i]["crtByrCnt"]["value"]
    #             order_buyers_rate = data_dict[i]["crtByrCnt"]["cycleCrc"] if data_dict[i]["crtByrCnt"][
    #                 "cycleCrc"] else 0.0
    #             order_conversion = data_dict[i]["crtRate"]["value"] if data_dict[i]["crtRate"]["value"] else 0.0
    #             order_conversion_rate = data_dict[i]["crtRate"]["cycleCrc"] if data_dict[i]["crtRate"][
    #                 "cycleCrc"] else 0.0
    #
    #             sql = """insert into traffic values(0, "map", '%s', %d, '%s', %d,
    #                     %f, %d, %f, %f, %f, %d, '%s')
    #                     """ % (i, statdate, pagename, visitors, visitors_rate, order_buyers,
    #                            order_buyers_rate, order_conversion, order_conversion_rate,
    #                            user_id, store_name)
    #
    #             obj.execute(sql)
    for key in data:
        for dict_data in data[key]:
            statdate = dict_data['statDate'] // 1000
            pagename = dict_data['pageName']
            visitors = dict_data['uv']
            order_buyers = dict_data['crtByrCnt']
            order_conversion = dict_data['crtRate']
            unit_price = dict_data['payPct']
            paynumber = dict_data['payByrCnt']

            sql = """insert into traffic (type,login,statdate,pagename,visitors,order_buyers,order_conversion,unit_price,paynumber,user,store_name) values ('map', '%s', %d, '%s', %d, %d, %f, %f, %d, %d, '%s')""" \
                  % (key, statdate, pagename, visitors, order_buyers, order_conversion, unit_price, paynumber, user_id,
                     store_name)

            obj.execute(sql)

    con.commit()  # 事物提交
    obj.close()
    con.close()


def ItemAnalysisSQL(data, user_id, store_name):
    """单品分析数据存储"""
    con, obj = Link_database()  # 链接数据库

    for dict_data in data:
        for a in dict_data['data']:
            if a == 'data':
                i = dict_data['data']
                # print(i)
                if i['data']:
                    statdate = i['data'][0]['statDate']['value'] // 1000
                    goods_id = int(i['data'][0]['item']['itemId'])
                    visitors = i['data'][0]['itmUv']['value']
                    try:
                        pay_conrersion_rate = str(i['data'][0]['payRate']['value'])
                    except Exception as e:
                        pay_conrersion_rate = '0'
                    try:
                        average_time = str(i['data'][0]['stayTimeAvg']['value'])
                    except Exception as e:
                        average_time = '0'
                    try:
                        out_rate = str(i['data'][0]['itmBounceRate']['value'])
                    except Exception as e:
                        out_rate = '0'
                    try:
                        pay_amount = str(i['data'][0]['payAmt']['value'])
                    except Exception as e:
                        pay_amount = '0'
                    try:
                        pay_buyers = i['data'][0]['payByrCnt']['value']
                    except Exception as e:
                        pay_buyers = 0
                    try:
                        pay_old_buyers = i['data'][0]['payOldByrCnt']['value']
                    except Exception as e:
                        pay_old_buyers = 0
                    try:
                        pay_a_number = i['data'][0]['payItmCnt']['value']
                    except Exception as e:
                        pay_a_number = 0
                    try:
                        purchased_numder = i['data'][0]['itemCartByrCnt']['value']
                    except Exception as e:
                        purchased_numder = 0
                    try:
                        access_age_amount = str(i['data'][0]['uvAvgValue']['value'])
                    except Exception as e:
                        access_age_amount = '0'
                    try:
                        collection_numder = i['data'][0]['itemCltByrCnt']['value']
                    except Exception as e:
                        collection_numder = 0
                    try:
                        refund_amount = str(i['data'][0]['sucRefundAmt']['value'])
                    except Exception as e:
                        refund_amount = '0'
                    try:
                        month_pay_a_number = i['data'][0]['mtdPayItmCnt']['value']
                    except Exception as e:
                        month_pay_a_number = 0
                    title = i['data'][0]['item']['title']

                    sql = """insert into itemanalysis (statdate,goods_id,visitors,pay_conrersion_rate,average_time,out_rate,pay_amount,
                              pay_buyers,pay_old_buyers,pay_a_number,purchased_numder,access_age_amount,collection_numder,
                              refund_amount,month_pay_a_number,title,user,store_name) values
                              (%d, %d, %d,'%s','%s','%s','%s',%d,%d,%d,%d,'%s',%d,'%s',%d,'%s',%d, '%s')""" \
                          % (statdate, goods_id, visitors, pay_conrersion_rate, average_time, out_rate, pay_amount,
                             pay_buyers, pay_old_buyers, pay_a_number, purchased_numder, access_age_amount,
                             collection_numder,
                             refund_amount, month_pay_a_number, title, user_id, store_name)
                    obj.execute(sql)

    con.commit()
    # time.sleep(0.01)
    obj.close()
    con.close()


def ItemKeywordsSQL(data, user_id, goods_id, store_name):
    """
    单品关键词数据存储
    """
    con, obj = Link_database()  # 链接数据库

    for data_list in data:
        for data_dict in data_list:
            statdate = data_dict['statDate'] // 1000  # 时间
            source = data_dict['keyword']  # 来源
            visitors = str(data_dict['uv'])  # 访客数
            jump_number = str(data_dict['jpSelfUv'])  # 店内跳转人数
            jump_out_number = str(data_dict['jpUv'])  # 店铺跳出人数
            collection_numder = str(data_dict['cltCnt'])  # 收藏人数
            purchased_numder = str(data_dict['cartByrCnt'])  # 加购人数
            pay_buyers = str(data_dict['payByrCnt'])  # 支付买家数
            fans_pay_buyers = str(data_dict['fansPayByrCnt'])  # 粉丝支付买家数
            collection_pay_buyers = str(data_dict['cltItmPayByrCnt'])  # 收藏商品支付买家数
            purchased_pay_buyers = str(data_dict['ordItmPayByrCnt'])  # 加购商品支付买家数
            unit_price = str(data_dict['unitprice'])  # 客单价

            sql = """insert into test_itemkeywords (statdate,source,visitors,jump_number,jump_out_number,collection_numder,
                      purchased_numder,pay_buyers,fans_pay_buyers,collection_pay_buyers,purchased_pay_buyers,unit_price,goods_id,user,store_name) 
                      values (%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,%d,'%s')""" % (
                statdate, source, visitors, jump_number,
                jump_out_number, collection_numder, purchased_numder, pay_buyers, fans_pay_buyers,
                collection_pay_buyers,
                purchased_pay_buyers, unit_price, goods_id, user_id, store_name)

            obj.execute(sql)

    con.commit()
    obj.close()
    con.close()


def ServiceIndicatorsSQL(data, user_id, store_name):
    """服务指标数据存储"""
    con, obj = Link_database()  # 链接数据库

    for data_dict in data:
        statdate = data_dict['data']['statDate']['value'] // 1000
        try:
            refund_rate = data_dict['data']['rfdEndTime']['value'] if data_dict['data']['rfdSucRate']['value'] else 0.00
        except:
            refund_rate = 0.00
        try:
            independent_refund_rate = data_dict['data']['rfdEndRate']['value'] if data_dict['data']['rfdEndRate'][
                'value'] else 0.00
        except:
            independent_refund_rate = 0.00
        try:
            disputes_refund_rate = data_dict['data']['rfdDsptRate']['value'] if data_dict['data']['rfdDsptRate'][
                'value'] else 0.00
        except:
            disputes_refund_rate = 0.00
        try:
            intervention = data_dict['data']['csInRfdRate']['value'] if data_dict['data']['csInRfdRate'][
                'value'] else 0.00
        except:
            intervention = 0.00
        try:
            complaints_rate = data_dict['data']['cmptRate']['value'] if data_dict['data']['cmptRate']['value'] else 0.00
        except:
            complaints_rate = 0.00
        try:
            refund_age_length = data_dict['data']['rfdAvgEndTime']['value'] if data_dict['data']['rfdAvgEndTime'][
                'value'] else 0.00
        except:
            refund_age_length = 0.00
        try:
            refund_return_age_length = data_dict['data']['rfdGoodsAvgEndTime']['value'] if \
                data_dict['data']['rfdGoodsAvgEndTime']['value'] else 0.00
        except:
            refund_return_age_length = 0.00
        try:
            ranking = data_dict['data']['svrRankExceedRate']['value'] if data_dict['data']['svrRankExceedRate'][
                'value'] else 0.00
        except:
            ranking = 0.00

        sql = """insert into serviceindicators values  (0, %d, '%f', %f, %f, %f, %f, %f, 
                      %f, %f, %d, '%s')""" % (statdate, refund_rate, independent_refund_rate, disputes_refund_rate,
                                              intervention, complaints_rate, refund_age_length,
                                              refund_return_age_length,
                                              ranking, user_id, store_name)

        obj.execute(sql)

    con.commit()
    obj.close()
    con.close()


def DryCargoSQL(title, article_simple, cover_photo, content, name):
    """论坛数据存储"""
    con, obj = Link_database()
    date = datetime.datetime.now().strftime('%Y-%m-%d')

    sql = """insert into dry_cargo_bbs (title,article_simple,cover_photo,content,published_date,published_name) values 
            ('%s', '%s', '%s', '%s', '%s', '%s')""" % (title, article_simple, cover_photo, content, date, name)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def UpdateDrySQL(title, article_simple, cover_photo, content, name, id):
    """修改文章数据"""
    con, obj = Link_database()
    # date = datetime.datetime.now().strftime('%Y-%m-%d')

    sql = """update dry_cargo_bbs set title='%s',article_simple='%s',cover_photo='%s',content='%s',published_name='%s' where id=%d""" \
          % (title, article_simple, cover_photo, content, name, id)

    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def LikeSQL(num, id):
    """点赞"""
    con, obj = Link_database()

    sql = """update dry_cargo_bbs set praise_number=%d where id=%d""" % (num, id)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def Category_SQL(data, date, user_id):
    """类目结构数据存储"""
    con, obj = Link_database()

    date = int(time.mktime(time.strptime(date, '%Y-%m')))

    for i in data:
        fname = str(i['fname'])
        catename = str(i['cateName'])
        payamt = str(i['payAmt'])
        payamtrate = str(i['payAmtRate'])
        paybuyercnt = i['payBuyerCnt']
        uvvarirate = str(i['payRate'])

        sql = """insert into category_structure (statdate,fname,catename,payamt,payamtrate,paybuyercnt,payRate, 
                user_id) values (%d, '%s', '%s', '%s', '%s', %d, '%s', %d)""" \
              % (date, fname, catename, payamt, payamtrate, paybuyercnt, uvvarirate, user_id)
        obj.execute(sql)

    con.commit()

    obj.close()
    con.close()


# def CrowdPortraitSQL(goods_id, gender_variance, age_variance, city_variance):
#     """人群画像存储"""
#     con, obj = Link_database()
#     statdate = int(time.time())
#     gender_str = '-'.join(gender_variance)
#     age_str = '-'.join(age_variance)
#     city_str = '-'.join(city_variance)
#
#     sql_1 = """select id from crowd_portrait where goods_id='%s' and  statdate='%s'""" % (goods_id, statdate)
#     obj.execute(sql_1)
#     if not obj.fetchall():
#         sql = """insert into crowd_portrait (statdate,goods_id,gender_variance,age_variance,city_variance) values ('%s', '%s', '%s', '%s', '%s')""" % (
#             statdate,goods_id,gender_str, age_str, city_str)
#         obj.execute(sql)
#         con.commit()
#
#     obj.close()
#     con.close()

def CrowdPortraitSQL(gender_variance, age_variance, city_variance):
    """人群画像存储"""
    con, obj = Link_database()

    gender_str = '-'.join(gender_variance)
    age_str = '-'.join(age_variance)
    city_str = '-'.join(city_variance)

    sql = """insert into crowd_portrait (gender_variance,age_variance,city_variance) values ('%s', '%s', '%s')""" % (
        gender_str, age_str, city_str)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()



class UserStatisticalSQL(object):
    """用户统计存储类"""
    def __init__(self, vip_level, statdate, upgrade_user, sms_contact, phone_contact, problem_feedback, problem_solve, diagnosis):
        self.vip_level = vip_level
        self.statdate = statdate
        self.upgrade_user = upgrade_user
        self.sms_contact = sms_contact
        self.phone_contact = phone_contact
        self.problem_feedback = problem_feedback
        self.problem_solve = problem_solve
        self.diagnosis = diagnosis
        self.con, self.obj = Link_database()

    def save_data(self):
        """存储数据"""
        sql = """insert into user_statistical (statdate,vip_level,upgrade_user,sms_contact,phone_contact,problem_feedback,problem_solve,diagnosis) values (%d,'%s',%d,%d,%d,%d,%d,%d)""" \
              % (self.statdate, self.vip_level, self.upgrade_user, self.sms_contact, self.phone_contact, self.problem_feedback, self.problem_solve, self.diagnosis)
        self.obj.execute(sql)
        self.con.commit()

    def __del__(self):
        self.obj.close()
        self.con.close()


if __name__ == '__main__':
    pass
a = 'new_user, to_user, upgrade_user, sms_contact, phone_contact, problem_feedback, problem_solve, diagnosis'
